package com.person.jc.service.base.verify;

import java.math.BigInteger;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Record;
import com.person.jc.common.SQLSpell;
import com.person.jc.service.Service;
import com.person.jc.utils.DateUtils;
import com.person.jc.utils.Utils;

public class BdVerifyBasisService extends Service {
	
	public final static BdVerifyBasisService me = new BdVerifyBasisService();
	
	public List<Record> getList(Map<String,Object> map){
		String temp = "";
		List<Object> params = new ArrayList<Object>();
		
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT t.*,\n");
		sql.append("       DATE_FORMAT(t.create_date, '%Y-%m-%d') create_date_,\n");
		sql.append("       DATE_FORMAT(t.valid_date, '%Y-%m-%d') valid_date_,\n");
		sql.append("       ("+SQLSpell.kindName("device_type", "t.device_type")+") device_type_name,\n");
		sql.append("       ("+SQLSpell.deviceName("t.device_id_")+") device_id_name,\n");
		sql.append("       DATEDIFF(valid_date, now()) diff_day\n");
		sql.append("  FROM bd_verify_basis t\n");
		sql.append(" WHERE if_valid = 1");
		if(Utils.isNotEmpty(temp = String.valueOf(map.get("verifyName")))){
			sql.append(" and t.verify_name like '%"+ temp +"%'");
		}
		if(Utils.isNotEmpty(temp = String.valueOf(map.get("deviceType")))){
			sql.append(" and t.device_type = '"+ temp +"'");
		}
		if(Utils.isNotEmpty(temp = String.valueOf(map.get("status")))){
			sql.append(" and t.status = '"+ temp +"'");
		}
		if(Utils.isNotEmpty(temp = String.valueOf(map.get("startDate")))){
			sql.append(" and DATE_FORMAT(t.create_date,'%Y-%m-%d') >= ?");
			params.add(temp);
		}
		if(Utils.isNotEmpty(temp = String.valueOf(map.get("endDate")))){
			sql.append(" and DATE_FORMAT(t.create_date,'%Y-%m-%d') <= ?");
			params.add(temp);
		}
		if(Utils.isNotEmpty(temp = String.valueOf(map.get("id")))){
			sql.append(" and t.id = '"+ temp +"'");
		}
		sql.append(" order by t.verify_name ");
		return Db.find(sql.toString(), Utils.getObjParam(params));
	}
	
	public String save(Map<String,Object> map){
		Object deviceId = Utils.isNotEmpty(String.valueOf(map.get("deviceId")))?map.get("deviceId"):null;
		Record record = new Record();
		record.set("verify_name", map.get("verifyName"));
		record.set("verify_no", map.get("verifyNo"));
		record.set("device_type", map.get("deviceType"));
		record.set("device_id_", deviceId);
		record.set("remark", map.get("remark"));
		record.set("create_date", DateUtils.getDateTime());
		record.set("valid_date", map.get("validDate"));
		record.set("status", 1);
		Db.save("bd_verify_basis", record);
		
		super.setCRUD("检定依据", "C", "新增检定依据，名称："+map.get("verifyName"));
		String idxSql = "SELECT LAST_INSERT_ID() from dual ";
		String id = Db.queryColumn(idxSql).toString();
		return id;
	}
	
	public void update(Map<String,Object> map){
		Object deviceId = Utils.isNotEmpty(String.valueOf(map.get("deviceId")))?map.get("deviceId"):null;
		Record record = new Record();
		record.set("id", map.get("id"));
		record.set("verify_name", map.get("verifyName"));
		record.set("verify_no", map.get("verifyNo"));
		record.set("device_type", map.get("deviceType"));
		record.set("device_id_", deviceId);
		record.set("remark", map.get("remark"));
		record.set("status", map.get("status"));
		record.set("valid_date", map.get("validDate"));
		Db.update("bd_verify_basis", "id", record);
		super.setCRUD("检定依据", "U", "修改检定依据，编号："+map.get("deviceId"));
	}
	
	public void delete(String id){
		String sql = " update bd_verify_basis set if_valid = 0 where id = ? ";
		Db.update(sql, id);
		super.setCRUD("检定依据", "D", "删除检定依据，编号："+id);
	}
	
	
	
	
	
	public List<Record> getStandList(Map<String,Object> map){
		String standard1s = String.valueOf(map.get("standard1s"));
		String standard2s = String.valueOf(map.get("standard2s"));
		String deviceType = String.valueOf(map.get("deviceType"));
		String deviceId = String.valueOf(map.get("deviceId"));
		
		StringBuffer sql = new StringBuffer();
		sql.append(" select * from ( ");
		sql.append(" select device_id id, 1 type,device_name name, '设备' type_name ,datediff(end_date , now()) diff_day,date_format(end_date, '%Y-%m-%d') end_date from bd_standard_device where if_valid = '1' ");
		if(Utils.isNotEmpty(standard1s)){
			sql.append(" and device_id not in ( ").append(standard1s).append(" )");
		}
		if(Utils.isNotEmpty(deviceType)){
			sql.append(" and device_type = '").append(deviceType).append("' ");
		}
		if(Utils.isNotEmpty(deviceId)){
			sql.append(" and device_id_ = '").append(deviceId).append("' ");
		}
		
		sql.append(" union");
		sql.append(" select matter_id id, 2 type,matter_name name, '物质' type_name,datediff(end_date , now()) diff_day,date_format(end_date, '%Y-%m-%d') end_date  from bd_standard_matter where if_valid = '1' ");
		if(Utils.isNotEmpty(standard2s)){
			sql.append(" and matter_id not in ( ").append(standard2s).append(" )");
		}
		if(Utils.isNotEmpty(deviceType)){
			sql.append(" and device_type = '").append(deviceType).append("' ");
		}
		if(Utils.isNotEmpty(deviceId)){
			sql.append(" and device_id_ = '").append(deviceId).append("' ");
		}
		sql.append(" )t where diff_day > 0 order by type, name");
		return Db.find(sql.toString());
	}
	
	public void deleteRel(String id){
		String sql = "delete from bd_verify_rel where verify_id = ?";
		Db.update(sql, id);
		super.setCRUD("检定依据", "D", "删除设备或物质，编号："+id);
	}
	
	public void saveRel(String vId, String[] ids, String type){
		String sql = "insert into bd_verify_rel(verify_id, standard_id, standard_type) values (?,?,?)";
		for (String id : ids) {
			Db.update(sql, vId, id, type);
		}
		super.setCRUD("检定依据", "C", "心中设备或物质，编号："+vId);
	}
	
	public List<Record> getRel(String id){
		String sql = "SELECT t1.* FROM bd_verify_rel t ,( ";
		sql += " SELECT device_id id, 1 TYPE,device_name NAME, '设备' type_name ,DATEDIFF(end_date , NOW()) diff_day,DATE_FORMAT(end_date, '%Y-%m-%d') end_date FROM bd_standard_device  ";
		sql += " UNION ";
		sql += " SELECT matter_id id, 2 TYPE,matter_name NAME, '物质' type_name,DATEDIFF(end_date , NOW()) diff_day,DATE_FORMAT(end_date, '%Y-%m-%d') end_date  FROM bd_standard_matter";
		sql += " ) t1 ";
		sql += " WHERE t.standard_id = t1.id AND t.standard_type = t1.type AND t.verify_id = ?";
		return Db.find(sql, id);
	}

	public void changeStatus(String id) {
		String sql = "update bd_verify_basis set status=(case when status=1 then 2 else 1 end)  where id = ?";
		Db.update(sql, id);
		super.setCRUD("检定依据", "U", "停用/启用检定依据，编号："+id);
	}
}
